package com.scau.eco.mapper;

import com.scau.eco.dto.CategoryAllDTO;
import com.scau.eco.entities.*;
import org.apache.ibatis.annotations.*;
import com.scau.eco.dto.CategoryDTO;


import java.util.List;


@Mapper
public interface DataMapper {

    /**
     * 获取所有的SessionAggrStat信息
     * @return
     */
//    @Results(id = "sessionAggrStatMap", value = {
//            @Result(property = "taskid", column = "task_id"),
//            @Result(property = "session_count", column = "session_count"),
//            @Result(property = "visit_length_1s_3s_ratio", column = "1s_3s"),
//            @Result(property = "visit_length_4s_6s_ratio", column = "4s_6s"),
//            @Result(property = "visit_length_7s_9s_ratio", column = "7s_9s"),
//            @Result(property = "visit_length_10s_30s_ratio", column = "10s_30s"),
//            @Result(property = "visit_length_30s_60s_ratio", column = "30s_60s"),
//            @Result(property = "visit_length_1m_3m_ratio", column = "1m_3m"),
//            @Result(property = "visit_length_3m_10m_ratio", column = "3m_10m"),
//            @Result(property = "visit_length_10m_30m_ratio", column = "10m_30m"),
//            @Result(property = "visit_length_30m_ratio", column = "30m"),
//            @Result(property = "step_length_1_3_ratio", column = "1_3"),
//            @Result(property = "step_length_4_6_ratio", column = "4_6"),
//            @Result(property = "step_length_7_9_ratio", column = "7_9"),
//            @Result(property = "step_length_10_30_ratio", column = "10_30"),
//            @Result(property = "step_length_30_60_ratio", column = "30_60"),
//            @Result(property = "step_length_60_ratio", column = "60")
//    })
    @Results(id = "sessionAggrStatMap", value = {
            @Result(property = "taskid", column = "taskid"),
            @Result(property = "session_count", column = "session_count"),
            @Result(property = "visit_length_1s_3s_ratio", column = "visit_length_1s_3s_ratio"),
            @Result(property = "visit_length_4s_6s_ratio", column = "visit_length_4s_6s_ratio"),
            @Result(property = "visit_length_7s_9s_ratio", column = "visit_length_7s_9s_ratio"),
            @Result(property = "visit_length_10s_30s_ratio", column = "visit_length_10s_30s_ratio"),
            @Result(property = "visit_length_30s_60s_ratio", column = "visit_length_30s_60s_ratio"),
            @Result(property = "visit_length_1m_3m_ratio", column = "visit_length_1m_3m_ratio"),
            @Result(property = "visit_length_3m_10m_ratio", column = "visit_length_3m_10m_ratio"),
            @Result(property = "visit_length_10m_30m_ratio", column = "visit_length_10m_30m_ratio"),
            @Result(property = "visit_length_30m_ratio", column = "visit_length_30m_ratio"),
            @Result(property = "step_length_1_3_ratio", column = "step_length_1_3_ratio"),
            @Result(property = "step_length_4_6_ratio", column = "step_length_4_6_ratio"),
            @Result(property = "step_length_7_9_ratio", column = "step_length_7_9_ratio"),
            @Result(property = "step_length_10_30_ratio", column = "step_length_10_30_ratio"),
            @Result(property = "step_length_30_60_ratio", column = "step_length_30_60_ratio"),
            @Result(property = "step_length_60_ratio", column = "step_length_60_ratio")
    })
    @Select(value = "SELECT * FROM session_aggr_stat")
    List<SessionAggrStat> getAllSessionAggrStat();

    /**
     * 根据任务ID获取某一个SessionAggrStat信息
     * @param taskId 任务ID
     * @return
     */
    @ResultMap(value = "sessionAggrStatMap")
    @Select(value = "SELECT * FROM session_aggr_stat WHERE taskid like concat('%',#{taskId},'%')")
    SessionAggrStat getSessionAggrStatByTaskId(String taskId);

    /**
     * 获取某个任务的随机行为
     * @param taskId
     * @return
     */
    @Results(id = "sessionRandomExtractMap",value = {
            @Result(property = "taskid", column = "taskid"),
            @Result(property = "sessionid", column = "sessionid"),
            @Result(property = "startTime", column = "startTime"),
            @Result(property = "searchKeywords", column = "searchkeywords"),
            @Result(property = "clickCategoryIds", column = "clickCategoryIds"),
    })
    @Select(value = "SELECT * FROM session_random_extract WHERE taskid like  concat('%',#{taskId},'%') ")
    List<SessionRandomExtract> getRandomExtractByTaskId(String taskId);

    /**
     * 获取所有随机行为
     * @return
     */
    @ResultMap(value = "sessionRandomExtractMap")
    @Select(value = "SELECT * FROM session_random_extract")
    List<SessionRandomExtract> getAllRandomExtract();

    /**
     * 获取所有的任务信息
     * @return
     */
    @Results(id = "taskMap", value = {
            @Result(property = "taskid", column = "taskid"),
            @Result(property = "taskName", column = "taskName"),
            @Result(property = "createTime", column = "createTime"),
            @Result(property = "startTime", column = "startTime"),
            @Result(property = "finishTime", column = "finishTime"),
            @Result(property = "taskType", column = "taskType"),
            @Result(property = "taskStatus", column = "taskStatus"),
            @Result(property = "taskParam", column = "taskParam")
    })
    @Select(value = "SELECT * FROM task")
    List<Task> getAllTaskInfo();

    /**
     * 根据任务ID查出具体任务
     * @param taskId
     * @return
     */
    @ResultMap(value = "taskMap")
    @Select(value = "SELECT * FROM task WHERE taskid like concat('%',#{taskId},'%')")
    List<Task> getTaskById(String taskId);

    /**
     * 查询categoryTOP10表所有数据
     * @return
     */
    @Results(value = {
            @Result(property = "taskId", column = "taskid"),
            @Result(property = "categoryid", column = "categoryid"),
            @Result(property = "data", column = "clickCount")
    })
    @Select(value = "SELECT taskid,categoryid,clickCount FROM top10_category;")
    List<CategoryDTO> getTopTenCategoryClickCount();

    @Results(value = {
            @Result(property = "taskId", column = "taskid"),
            @Result(property = "data", column = "orderCount")
    })
    @Select(value = "SELECT taskid,categoryid,orderCount FROM top10_category;")
    List<CategoryDTO> getTopTenCategoryOrderCount();

    @Results(value = {
            @Result(property = "taskId", column = "taskid"),
            @Result(property = "data", column = "payCount")
    })
    @Select(value = "SELECT taskid,categoryid,payCount FROM top10_category;")
    List<CategoryDTO> getTopTenCategoryPayCount();

    /**
     * 获取所有Session信息
     * @return
     */
    @Results(id = "topSessionMap", value = {
            @Result(property = "taskid", column = "taskid"),
            @Result(property = "categoryid", column = "categoryid"),
            @Result(property = "sessionid", column = "sessionid"),
            @Result(property = "clickCount", column = "clickCount")
    })
    @Select(value = "SELECT * FROM top10_session order by clickCount desc")
    List<Top10Session> getTopTenSession();

    /**
     * 根据任务ID获取具体的session信息
     * @param categoryid
     * @return
     */
    @ResultMap(value = "topSessionMap")
    @Select(value = "SELECT * FROM top10_session WHERE categoryid = #{categoryid} order by clickCount desc")
    List<Top10Session> getTopTenSessionByTaskId(Long categoryid);

    /**
     * 获取所有的页面分割转换率
     * @return
     */
    @Results(id = "pageSplitConvertRateMap", value = {
                @Result(property = "taskId", column = "taskid"),
                @Result(property = "convertRate", column = "convertRate")
    })
    @Select(value = "SELECT * FROM page_split_convert_rate")
    List<PageSplitConvertRate> getPageSplitConvertRate();


    @Results(id = "areaTop3Product",value = {
            @Result(property = "taskid", column = "taskid")
            //@Result(property = "areaL")
    })
    @Select(value = "select * from area_top3_product where area = #{area}")
    List<AreaTop3Product> getAreaTop3ProductData(String area);

    @Select(value = "select * from ad_blackList")
    List<BlackUser> getBlackUserData();

    @Select(value = "select * from ad_province_top3 where province like concat('%',#{province},'%') order by date desc, clickCount desc" )
    List<ProvinceTop3Ad> getProvinceTop3AdData(String province);

    @Select(value = "select * from ad_province_top3 order by clickCount desc" )
    List<ProvinceTop3Ad> getAllProvinceTop3AdData();

    /**
     * 最近一小时广告点击量实时统计
     * @return
     */
    @Results(id = "AdClickTrendMap", value = {
            @Result(property = "date", column = "date"),
            @Result(property = "hour", column = "hour"),
            @Result(property = "minute", column = "minute"),
            @Result(property = "adid", column = "adid"),
            @Result(property = "clickCount", column = "clickCount"),
    })
    @Select(value = "SELECT * FROM ad_click_trend order by minute desc")
    List<AdClickTrend> getAdClickTrendByGroup();

    /**
     * 根据adid统计最近一小时广告点击量实时统计
     * @return
     */
    @ResultMap(value = "AdClickTrendMap")
    @Select("SELECT `adid`,`hour`,`minute`,IFNULL(SUM(clickCount),0) AS clickCount " +
            "FROM ad_click_trend " +
            "GROUP BY `hour`,`minute`,adid " +
            "ORDER BY `adid` asc,`hour` asc,`minute` asc")
    @Results(id = "AdClickTrendMap", value = {
            @Result(property = "hour", column = "hour"),
            @Result(property = "minute", column = "minute"),
            @Result(property = "adid", column = "adid"),
            @Result(property = "clickCount", column = "clickCount"),
    })
    List<AdClickTrend> getAdClickTrendByGroupTimeAndId();


    /**
     * 获取当前所有的广告id
     * @return
     */
    @Select("SELECT DISTINCT(adid) AS adID " +
            "FROM ad_click_trend ")
    List<String> getAllAdId();

    /**
     * 获取当前所有的时段
     * @return
     */
    @Select("SELECT CONCAT(`hour`,'时',`minute`,'分') as timeInterval " +
            "FROM ad_click_trend " +
            "GROUP BY `hour`,`minute`")
    List<String> getAllTimeInterval();


    @Select("select ad.adid,ad.clickCount,i.province,i.city " +
            "from ad_stat ad left join ipc i on ad.province = i.id " +
            "where i.province = #{province} and i.city = #{city} " +
            "order by adid ")
    List<AdStat> getAdStatData(String province,String city);


    @Results(value = {
            @Result(property = "taskId", column = "taskid"),
    })
    @Select(value = "SELECT * FROM top10_category")
    List<CategoryAllDTO> getTopTenCategoryAll();
}
